package com.model.operation;

import com.Table.Movie;
import com.Table.User;
import com.model.DB;

import java.sql.*;
import java.util.ArrayList;


/**
 * Created by lwx on 2016/12/3.
 */
public class UserOperate {
    /*
    * 登录的时候判断账号密码是否正确
    * 2016-12-4
    * */
    public String IsUserValid(String username, String password) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs;
        StringBuilder sql = new StringBuilder("select * from user where username = '" + username + "'");
        try {
            //通过账号查询数据库
            con = getCon();
            pst = con.prepareStatement(sql.toString());
            rs = pst.executeQuery();
            //如果有值，代表用户账号存在，检测密码
            if (rs.next()) {
                //获取密码值，与参数作比较，返回结果
                String passwd = rs.getString("passwd");
                if (password.equals(passwd)) {
                    return "success";
                } else {
                    return "passwdError";
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst);
        }
        //通过账号查询，没有值代表不存在该账号信息
        return "usernameError";
    }

    /*
    * 注册的时候判断账号是否存在，如果不存在把新用户信息插入数据库
    * */
    public String IsUsernameValid(String username, String password) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs;
        Date signupdate = new Date(System.currentTimeMillis());
        StringBuilder sqlcheck = new StringBuilder("select * from user where username = '" + username + "'");
        StringBuilder sqlinsert = new StringBuilder("insert into user(username,passwd,coinnum,signupdate) values('"
                + username + "','" + password + "',5,'" + signupdate + "')");
        boolean isexists = true;
        try {
            //通过账号查询数据库
            con = getCon();
            pst = con.prepareStatement(sqlcheck.toString());
            rs = pst.executeQuery();
            //如果有值，代表用户账号存在，
            if (rs.next()) {
                //账号已经存在
                return "fail";
            } else {   //用户账号还不存在
                isexists = false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst);
        }
        //如果用户账号不存在则插入
        if (!isexists) {
            try {
                con = getCon();
                pst = con.prepareStatement(sqlinsert.toString());
                //pst.executeQuery();
                pst.execute();
                return "success";
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (IllegalArgumentException e) {
                e.printStackTrace();
            } finally {
                close(con, pst);
            }
        }
        //如果到达这一步则操作失败
        return "fail";
    }

    /*
    * 修改个人信息
    * */
    public String UpdateSelfInformation(String username, String password,
                                        String nickname, String sex,
                                        String signature) {
        Connection con = null;
        PreparedStatement pst = null;
        StringBuilder sql = new StringBuilder("update user set passwd='" + password + "',nickname='" + nickname
                + "',sex='" + sex + "',signature='" + signature + "'" + "where username = '" + username + "'");
        try {
            //修改数据
            con = getCon();
            pst = con.prepareStatement(sql.toString());
            pst.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst);
        }

        return "success";
    }

    /*
    * 查看个人信息
    * */
    public User CheckSelfInformation(String username) {
        User UserInformation = new User();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        StringBuilder sql = new StringBuilder("select * from user where username = '" + username + "'");
        try {
            //通过账号查询数据库
            con = getCon();
            pst = con.prepareStatement(sql.toString());
            rs = pst.executeQuery();
            //如果有值，代表用户账号存在，取个人信息
            if (rs.next()) {
                //获取密码值，与参数作比较，返回结果
                String passwd = rs.getString("passwd");
                String sex = rs.getString("sex");
                String nickname = rs.getString("nickname");
                String signature = rs.getString("signature");
                int coinnum = rs.getInt("coinnum");
                int userID = rs.getInt("userID");
                Date signupdate = rs.getDate("signupdate");
                long time = signupdate.getTime();
                //保存到类中
                UserInformation = new User(
                        userID,
                        username,
                        passwd,
                        nickname,
                        sex,
                        coinnum,
                        signature,
                        time
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst);
        }
        //通过账号查询，没有值代表不存在该账号信息
        return UserInformation;
    }

    /*
    * 投币
    * */
    public String AddCoin(String username, String moviename) {
        int userID = -1;
        int movieID = -1;
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        StringBuilder checkuserID = new StringBuilder("select * from user where username='" + username + "'");
        StringBuilder checkmovieID = new StringBuilder("select * from movie where moviename='" + moviename + "'");

        //查询是否已经投币
        try {
            con = getCon();
            //取出用户ID
            pst = con.prepareStatement(checkuserID.toString());
            rs = pst.executeQuery();
            if (rs.next()) {
                userID = rs.getInt("userID");
            }
            //取出电影ID
            pst = con.prepareStatement(checkmovieID.toString());
            rs = pst.executeQuery();
            if (rs.next()) {
                movieID = rs.getInt("movieID");
            }
            //判断是否已经投币
            StringBuilder isHadAddCoin = new StringBuilder("select * from user_movie_coin where userID = '" + userID +
                    "'and movieID='" + movieID + "'");
            pst = con.prepareStatement(isHadAddCoin.toString());
            rs = pst.executeQuery();
            if (rs.next()) {
                //已经投币，返回错误
                return "fail";
            } else {
                StringBuilder AddElem = new StringBuilder("insert into user_movie_coin values(" + userID + "," + movieID + ")");
                pst = con.prepareStatement(AddElem.toString());
                pst.execute();
                StringBuilder movieCoinNumAdd = new StringBuilder("update movie set coinnum=coinnum+1 where movieID=" + movieID);
                pst = con.prepareStatement(movieCoinNumAdd.toString());
                pst.execute();
                return "success";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst);
        }
        return "fail";
    }

    /*
    * 观看历史
    * */
    public String AddUserMovieHistory(String username, String moviename, Integer percent) {
        int userID = -1;
        int movieID = -1;
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        StringBuilder checkuserID = new StringBuilder("select * from user where username='" + username + "'");
        StringBuilder checkmovieID = new StringBuilder("select * from movie where moviename='" + moviename + "'");

        //查询是否已经加入记录
        try {
            con = getCon();
            //取出用户ID
            pst = con.prepareStatement(checkuserID.toString());
            rs = pst.executeQuery();
            if (rs.next()) {
                userID = rs.getInt("userID");
            } else {
                return "fail";
            }
            //取出电影ID
            pst = con.prepareStatement(checkmovieID.toString());
            rs = pst.executeQuery();
            if (rs.next()) {
                movieID = rs.getInt("movieID");
            } else {
                return "fail";
            }

            StringBuilder AddElem = new StringBuilder(
                    String.format("insert into user_movie_history " +
                            "(userID, movieID, percent) " +
                            "values(%d,%d,%d)", userID, movieID, percent));
            // if has elem, then update it
            AddElem.append(String.format(" ON DUPLICATE KEY UPDATE percent = %d", percent));
            pst = con.prepareStatement(AddElem.toString());
            pst.execute();
            return "success";
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst);
        }
        return "fail";
    }

    /*
    * 关注（保留）
    * */
    public String UserMovieAttention(String username, String moviename) {
        int userID = -1;
        int movieID = -1;
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        StringBuilder checkuserID = new StringBuilder("select * from user where username='" + username + "'");
        StringBuilder checkmovieID = new StringBuilder("select * from movie where moviename='" + moviename + "'");

        //查询是否已经加入记录
        try {
            con = getCon();
            //取出用户ID
            pst = con.prepareStatement(checkuserID.toString());
            rs = pst.executeQuery();
            if (rs.next()) {
                userID = rs.getInt("userID");
            }
            //取出电影ID
            pst = con.prepareStatement(checkmovieID.toString());
            rs = pst.executeQuery();
            if (rs.next()) {
                movieID = rs.getInt("movieID");
            }
            //判断改用户观看该电影的记录是否已经加入
            StringBuilder isHadAddElem = new StringBuilder("select * from user_movie_attention where userID = '" + userID +
                    "'and movieID='" + movieID + "'");
            pst = con.prepareStatement(isHadAddElem.toString());
            rs = pst.executeQuery();
            if (rs.next()) {
                //已经加入记录，返回错误
                return "fail";
            } else {
                StringBuilder AddElem = new StringBuilder("insert into user_movie_attention values(" + userID + "," + movieID + ")");
                pst = con.prepareStatement(AddElem.toString());
                pst.execute();
                return "success";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst);
        }
        return "fail";
    }

    /*
    * 用户观看视频的历史记录
    * */
    public ArrayList<Movie> CheckUserWatchMovieHistory(String username, int num,
                                                       int pages, String type) {
        int userID = -1;
        ArrayList<Movie> movies = new ArrayList<Movie>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        StringBuilder checkuserID = new StringBuilder("select * from user where username='" + username + "'");

        //查询是否已经加入记录
        try {
            con = getCon();
            //取出用户ID
            pst = con.prepareStatement(checkuserID.toString());
            rs = pst.executeQuery();
            if (rs.next()) {
                userID = rs.getInt("userID");
            }
            //查看观看过的电影ID
            StringBuilder CheckHistory =
                    //select num videos from the pages's page
                    new StringBuilder("select * from user_movie_history, movie " +
                            "where user_movie_history.userID = '" + userID +
                            "'and user_movie_history.movieID = movie.movieID" +
                            "Limit " + (pages - 1) * num + "," + num);
            pst = con.prepareStatement(CheckHistory.toString());
            rs = pst.executeQuery();
            while (rs.next()) {
                //已经加入记录，返回错误
                Integer movieID = rs.getInt("movie.movieID");
                String moviename = rs.getString("moviename");
                Integer userIDitem = rs.getInt("movie.userID");
                Integer coinnum = rs.getInt("coinnum");
                Timestamp uploadtime = rs.getTimestamp("uploadtime");
                String tag = rs.getString("tag");
                String description = rs.getString("description");
                String address = rs.getString("address");
                String picture = rs.getString("picture");
                Integer percent = rs.getInt("percent");
                Movie item = new Movie(movieID, moviename, userIDitem, coinnum,
                        uploadtime, tag, description, address, picture, percent);
                movies.add(item);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return movies;
    }

    /*
    * 用户投币历史记录
    * */
    public ArrayList<Movie> CheckUserAddCoinHistory(String username,
                                                    Integer num, Integer pages) {
        int userID = -1;
        ArrayList<Movie> movies = new ArrayList<Movie>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        StringBuilder checkuserID = new StringBuilder("select * from user where username='" + username + "'");

        //查询是否已经加入记录
        try {
            con = getCon();
            //取出用户ID
            pst = con.prepareStatement(checkuserID.toString());
            rs = pst.executeQuery();
            if (rs.next()) {
                userID = rs.getInt("userID");
            }
            //查看观看过的电影ID
            StringBuilder CheckHistory = new StringBuilder(
                    "select * from user_movie_coin,movie " +
                            "where user_movie_coin.userID = '" + userID
                            + "'and user_movie_coin.movieID=movie.movieID" +
                            "Limit " + (pages - 1) * num + "," + num);
            pst = con.prepareStatement(CheckHistory.toString());
            rs = pst.executeQuery();
            while (rs.next()) {
                //已经加入记录，返回错误
                Movie item = MovieOperate.getMovie(rs);
                movies.add(item);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst);
        }
        return movies;
    }

    private Connection getCon() throws ClassNotFoundException, SQLException {
        DB.getInstance();
        return DB.getConnection();
    }

    private void close(Connection con, PreparedStatement pst, ResultSet rs) {
        try {
            if (rs != null)
                rs.close();
            if (pst != null)
                pst.close();
            if (con != null)
                DB.release(con);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 私有方法，关闭数据库操作2,用于更新数据库操作
    private void close(Connection con, PreparedStatement pst) {
        try {
            if (pst != null)
                pst.close();
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
